from dbutils.pooled_db import PooledDB
import pymysql
from db_config import DB_CONFIG
from pymysql.cursors import DictCursor
# 创建数据库连接池（避免频繁创建/关闭连接）
pool = PooledDB(
    creator=pymysql,        # 数据库驱动
    maxconnections=10,      # 最大连接数
    mincached=2,            # 最小空闲连接
    maxcached=5,            # 最大空闲连接
    blocking=True,          # 连接耗尽时是否阻塞等待
    setsession=[],          # 会话参数（如开启事务）
    ping=0,                 # 检查连接有效性（0=不检查）
    cursorclass=DictCursor,  # 显式指定游标为DictCursor
    **DB_CONFIG             # 从配置文件导入连接信息
)

def get_connection():
    """获取数据库连接（从连接池）"""
    return pool.connection()

def query_one(sql, args=None):
    """查询单条数据（返回字典）"""
    conn = None
    cursor = None
    try:
        conn = get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)  # 返回字典格式
        cursor.execute(sql, args)
        return cursor.fetchone()  # 返回单条数据（None表示无结果）
    except Exception as e:
        print(f"查询失败：{e}，SQL: {sql}，参数: {args}")
        return None
    finally:
        # 释放资源
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def execute_sql(sql, args=None):
    """执行增删改操作（返回影响行数）"""
    conn = None
    cursor = None
    try:
        conn = get_connection()
        cursor = conn.cursor()
        affected_rows = cursor.execute(sql, args)
        conn.commit()  # 提交事务（增删改必须提交）
        return affected_rows
    except Exception as e:
        if conn:
            conn.rollback()  # 失败回滚事务
        print(f"执行失败：{e}，SQL: {sql}，参数: {args}")
        return 0
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def query_all(sql, args=None):
    """查询多条数据（返回列表字典）"""
    conn = None
    cursor = None
    try:
        conn = get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute(sql, args)
        return cursor.fetchall()  # 返回多条数据（空列表表示无结果）
    except Exception as e:
        print(f"查询失败：{e}，SQL: {sql}，参数: {args}")
        return []
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_video_id_by_url(video_url):
    """根据视频URL查询对应的video_id（整数）"""
    conn = None
    try:
        conn = pool.connection()  # 从连接池获取连接
        cursor = conn.cursor()
        cursor.execute("SELECT id FROM videos WHERE video_url = %s", (video_url,))
        result = cursor.fetchone()
        return result['id'] if result else None  # 存在则返回ID，否则返回None
    except Exception as e:
        print(f"查询video_id失败: {e}")
        return None
    finally:
        if conn:
            conn.close()  # 释放连接回池
def query_video_sources(openid, video_url):
    """根据用户微信唯一id和视频url查询数据库中的视频源信息"""
    conn = None
    try:
        # 从连接池获取连接
        conn = pool.connection()
        cursor = conn.cursor()
        # 执行查询语句，获取视频源信息
        cursor.execute("SELECT * FROM videos WHERE user_id = %s AND video_id = %s", (openid, video_url))
        video_sources = cursor.fetchall()
        print(video_sources)
        cursor.execute("select create_time from users where openid=%s", (openid))
        user_create_time = cursor.fetchone()
        print(user_create_time)
        return {"video_sources": video_sources,
                "user_create_time": user_create_time
                }
    except Exception as e:
        print(f"数据库查询错误: {e}")
        return []
    finally:
        # 释放连接回连接池
        if conn:
            conn.close()

# 用户视频列表查询接口
def query_videos_list(openid):
    """根据用户微信唯一id查询数据库中的视频源信息"""
    conn = None
    try:
        # 从连接池获取连接
        conn = pool.connection()
        cursor = conn.cursor()
        # 执行查询语句，获取视频源信息
        cursor.execute("SELECT * FROM videos WHERE userid = %s ", (openid))
        video_list = cursor.fetchall()
        print(video_list)
        return video_list
    except Exception as e:
        print(f"数据库查询错误: {e}")
        return []
    finally:
        # 释放连接回连接池
        if conn:
            conn.close()

#根据openid查询数据库是否有对应记录
def is_new_user(openid):
    conn = None
    try:
        # 从连接池获取连接
        conn = pool.connection()
        cursor = conn.cursor()
        # 执行查询语句，获取视频源信息
        if cursor.execute("select name from users where openid = %s",{openid,}):
            return False  #如果数据库中存在该openid 返回False
        return True   #如果数据库中不存在该openid 返回True
    except Exception as e:
        print(f"数据库查询错误: {e}")
        return []
    finally:
        # 释放连接回连接池
        if conn:
            conn.close()

#根据race_number和personid插入openid等信息
def insert_openid_by_raceNumber_personId(openid,race_number,personid):
    conn = None
    try:
        # 从连接池获取连接
        conn = pool.connection()
        cursor = conn.cursor()
        #1. 检查personid后4位是否匹配
        cursor.execute("SELECT SUBSTRING(personid, 15, 4) FROM users WHERE race_number = %s", (race_number,))
        result = cursor.fetchone()
        print(result['SUBSTRING(personid, 15, 4)'])
        if result['SUBSTRING(personid, 15, 4)'] == personid:
            update_count = cursor.execute("update users set openid = %s where race_number = %s",(openid,race_number))
            print(update_count)
            cursor.execute("select video_url from videos where userid = %s",(openid,))
            original_video_url = cursor.fetchone()
            conn.commit()
            print(original_video_url)
            print(type(original_video_url))
            return original_video_url['video_url']
    except Exception as e:
        print(f"数据库查询错误: {e}")
        return []
    finally:
        # 释放连接回连接池
        if conn:
            conn.close()

#根据body_effects_id和bg_effects_id查询对应的特效名称
def get_effects_name_by_body_effects_id_and_bg_effects_id(body_effects_id,bg_effects_id):
    conn = None
    try:
        # 从连接池获取连接
        conn = pool.connection()
        cursor = conn.cursor()
        # 执行查询语句，获取视频源信息
        cursor.execute("select effect_title from effects where id = %s",(body_effects_id,))
        body_effects_name = cursor.fetchone()
        cursor.execute("select effect_title from effects where id = %s", (bg_effects_id,))
        bg_effects_name = cursor.fetchone()
        return {
            "body_effects_name":body_effects_name,
            "bg_effects_name":bg_effects_name
        }
    except Exception as e:
        print(f"数据库查询错误: {e}")
        return []
    finally:
        # 释放连接回连接池
        if conn:
            conn.close()
